<?php

class Scenery_base_model extends CI_Model
{
    private $table = 'scenery_base';
    private $table_2 = 'scenery_info';
    private $table_3 = 'goods_base';

    public function findById($sid)
    {
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sId=scenery_base.sid', 'INNER');
        $this->db->where('scenery_base.bit_state', 1);
        $this->db->where('scenery_base.sid', $sid);
        $this->db->limit(1);

        return $this->db->get();
    }

    public function findBySid($sid)
    {
        $this->db->where('sid', $sid);
        $this->db->where('bit_state', 1);
        $this->db->limit(1);

        return $this->db->get($this->table);
    }

    /**
     * 特价产品
     * @param unknown $getData
     * @param unknown $num
     */
    public function sceneryOnSal($getData, $limit = 4)
    {
        $this->db->select('MIN(goods_base.shop_price) AS shop_price, SUM(goods_base.sold_number) AS sales_volume, SUM(goods_base.advance_date) AS advance_date, SUM(goods_base.off_bonus) AS off_bonus, goods_base.market_price, scenery_base.sid, category_id, scenery_base.name_hanzi, scenery_base.name_pinyin, scenery_base.provinceid, scenery_base.cityid, scenery_base.areaid, scenery_base.address, scenery_base.star_rank, scenery_base.timestamp, scenery_info.minipics, scenery_base.click_count');
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sid=scenery_base.sid', 'INNER');
        $this->db->join($this->table_3 . ' AS goods_base', 'goods_base.sid=scenery_base.sid AND goods_base.is_check=2 AND goods_base.bit_opt&1=1', 'INNER');
        $this->db->where('scenery_base.bit_state', 1);
        //$this->db->where('scenery_base.flag_1', 1);
        $this->db->where('goods_base.click_count !=', 1, false); //1:前台隐藏
        if ( !empty($getData['cityId']) ) {
            $this->db->where('scenery_base.cityid', $getData['cityId']);
        }
        if ( !empty($getData['provinceid']) ) {
            $this->db->where('scenery_base.provinceid', $getData['provinceid']);
        }
        if (!empty($getData['is_scan'])) {
            $this->db->where('goods_base.is_scan', $getData['is_scan']); //1:网络预订 2:扫码订票
        }
        $this->db->group_by('scenery_base.sid');
        $this->db->order_by('scenery_base.flag_1 DESC');
        $this->db->order_by('sales_volume DESC');
        $this->db->limit($limit);

        return $this->db->get();
    }

    /**
     * 热销产品（不含特价）
     * @param unknown $getData
     * @param unknown $num
     * @param string $orderBy
     */
    public function sceneryRecommend($getData, $page_num, $num)
    {
        $this->db->select('MIN(goods_base.shop_price) AS shop_price, SUM(goods_base.sold_number) AS sales_volume, SUM(goods_base.advance_date) AS advance_date, SUM(goods_base.off_bonus) AS off_bonus, goods_base.market_price, scenery_base.sid, category_id, scenery_base.name_hanzi, scenery_base.name_pinyin, scenery_base.provinceid, scenery_base.cityid, scenery_base.areaid, scenery_base.address, scenery_base.star_rank, scenery_base.timestamp, scenery_info.minipics');
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sid=scenery_base.sid', 'INNER');
        $this->db->join($this->table_3 . ' AS goods_base', 'goods_base.sid=scenery_base.sid AND goods_base.is_check=2 AND goods_base.bit_opt&1=1', 'INNER');
        $this->db->where('scenery_base.bit_state', 1); //上架
        $this->db->where('scenery_base.flag_1 !=', 1);
        $this->db->where('goods_base.click_count !=', 1, false); //1:前台隐藏
        if ( !empty($getData['cityId']) ) {
            $this->db->where_in('scenery_base.cityid', $getData['cityId']);
        }
        if ( !empty($getData['provinceid']) ) {
            $this->db->where('scenery_base.provinceid', $getData['provinceid']);
        }
        if (!empty($getData['is_scan'])) {
            $this->db->where('goods_base.is_scan', $getData['is_scan']); //1:网络预订 2:扫码订票
        }
        $this->db->group_by('`scenery_base`.`sid`');
        $this->db->order_by('SUM(goods_base.sold_number) DESC, scenery_base.cityid ASC');
        $this->db->limit($page_num, $num);

        return $this->db->get();
    }

    /**
     * 所有上架产品
     * @param unknown $params
     * @param unknown $num
     */
    public function sceneryHotTickets($params = array())
    {
        $this->db->select('MIN(goods_base.shop_price) AS shop_price, SUM(goods_base.sold_number) AS sales_volume, SUM(goods_base.advance_date) AS advance_date, SUM(goods_base.off_bonus) AS off_bonus, goods_base.market_price, scenery_base.sid, scenery_base.uid, category_id, scenery_base.name_hanzi, scenery_base.name_english, scenery_base.provinceid, scenery_base.cityid, scenery_base.areaid, scenery_base.address, scenery_base.star_rank, scenery_base.timestamp, scenery_info.minipics');
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sid=scenery_base.sid', 'INNER');
        $this->db->join($this->table_3 . ' AS goods_base', 'goods_base.sid=scenery_base.sid AND goods_base.is_check=2 AND goods_base.bit_opt&1=1', 'INNER');
        $this->db->where('scenery_base.bit_state', 1); //上架
        $this->db->where('goods_base.click_count !=', 1, false); //1:前台隐藏
        if ( !empty($params['province_id']) ) {
            $this->db->where('scenery_base.provinceid', (int)$params['province_id']);
        }
        if ( !empty($params['city_id']) ) {
            $this->db->where('scenery_base.cityid', (int)$params['city_id']);
        }
        if ( !empty($params['cityids']) ) {
            $this->db->where_in('scenery_base.cityid', $params['cityids']);
        }
        if ( !empty($params['category_id']) ) {
            $this->db->where('scenery_base.category_id', (int)$params['category_id']);
        }
        if ( !empty($params['not_sid']) ) {
            $this->db->where('scenery_base.sid !=', (int)$params['not_sid'], false);
        }

        if ( !empty($params['sid'])) {
            $this->db->where_in('scenery_base.sid', $params['sid']);
        }
        if (!empty($params['is_scan'])) {
            $this->db->where('goods_base.is_scan', $params['is_scan']); //1:网络预订 2:扫码订票
        }
        $this->db->group_by('scenery_base.sid');
        $this->db->order_by('sales_volume', 'DESC');
        if ( !empty($params['limit']) ) {
            $this->db->limit((int)$params['limit']);
        }

        return $this->db->get();
    }

    public function search($getData, $num, $start)
    {
        $sql = '';
        if ( !empty($getData['bonus']) ) {
            $sql .= ' AND goods_base.off_bonus>0';
        }
        if ( !empty($getData['today']) ) { //当天可定
            $sql .= ' AND goods_base.advance_date=0';
        }

        $this->db->select('MIN(goods_base.shop_price) AS shop_price, SUM(goods_base.sold_number) AS sales_volume, SUM(goods_base.advance_date) AS advance_date, off_bonus, goods_base.market_price, scenery_base.sid, category_id, scenery_base.name_hanzi, scenery_base.name_pinyin, scenery_base.provinceid, scenery_base.cityid, scenery_base.areaid, scenery_base.address, scenery_base.star_rank, scenery_base.timestamp, scenery_info.minipics');
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sid=scenery_base.sid', 'INNER');
        $this->db->join($this->table_3 . ' AS goods_base', 'goods_base.sid=scenery_base.sid AND goods_base.is_check=2 AND goods_base.bit_opt&1=1 ' . $sql, 'INNER');
        $this->db->where('scenery_base.bit_state', 1); //上架
        $this->db->where('goods_base.click_count !=', 1, false); //1:前台隐藏
        if ( !empty($getData['special']) ) { // 1：特价活动
            $this->db->where_in('scenery_base.flag_1', $getData['special']);
        }
        if (!empty($getData['is_scan'])) {
            $this->db->where('goods_base.is_scan', $getData['is_scan']); //1:网络预订 2:扫码订票
        }
        if ( !empty($getData['keyword']) ) {
            $keyword = trim(addslashes($getData['keyword']));
            $this->db->where("(scenery_base.name_hanzi LIKE '%{$keyword}%' OR scenery_base.address LIKE '%{$keyword}%')");
        }

        if ( !empty($getData['theme']) ) {
            $this->db->where('scenery_base.category_id', $getData['theme']);
        }

        if ( !empty($getData['provinceid']) ) {
            $this->db->where('scenery_base.provinceid', $getData['provinceid']);
        }

        if ( !empty($getData['cityid']) ) {
            $this->db->where('scenery_base.cityid', $getData['cityid']);
        }

        if ( !empty($getData['sort']) ) {
            switch ($getData['sort']) {
                case 1: //综合排序
                    $this->db->order_by('scenery_base.click_count', 'DESC');
                    break;
                case 2: //销量最高
                    $this->db->order_by('SUM(goods_base.sold_number)', 'DESC');
                    break;
                case 3: //价格从高到底
                    $this->db->order_by('MIN(goods_base.shop_price)', 'DESC');
                    break;
                case 4: //价格从底到高
                    $this->db->order_by('MIN(goods_base.shop_price)', 'ASC');
                    break;
                case 5: //金币从低到高
                    $this->db->order_by('MIN(goods_base.off_bonus)', 'DESC');
                    break;
                default:
                    break;
            }
        }
        $this->db->group_by('scenery_base.sid');
        $this->db->limit($num, $start);

        return $this->db->get();
    }


    /**
     * 热门区域产品
     * @param unknown $params
     */
    public function getSceneryList($params = array())
    {
        $this->db->select('MIN(goods_base.shop_price) AS shop_price, SUM(goods_base.sold_number) AS sales_volume, SUM(goods_base.advance_date) AS advance_date, SUM(goods_base.off_bonus) AS off_bonus, goods_base.market_price, scenery_base.sid, category_id, scenery_base.name_hanzi, scenery_base.name_pinyin, scenery_base.provinceid, scenery_base.cityid, scenery_base.areaid, scenery_base.address, scenery_base.star_rank, scenery_base.timestamp, scenery_info.minipics');
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sid=scenery_base.sid', 'INNER');
        $this->db->join($this->table_3 . ' AS goods_base', 'goods_base.sid=scenery_base.sid AND goods_base.is_check=2 AND goods_base.bit_opt&1=1', 'INNER');
        if ( !empty($params['sids']) ) {
            $this->db->where_in('scenery_base.sid', $params['sids']);
        }
        if ( !empty($params['provinceids']) ) {
            $this->db->where_in('scenery_base.provinceid', $params['provinceids']);
        }
        if ( !empty($params['cityids']) ) {
            $this->db->where_in('scenery_base.cityid', $params['cityids']);
        }
        if ( !empty($params['areaids']) ) {
            $this->db->where_in('scenery_base.areaid', $params['areaids']);
        }
        if (!empty($params['is_scan'])) {
            $this->db->where('goods_base.is_scan', $params['is_scan']); //1:网络预订 2:扫码订票
        }
        $this->db->where('scenery_base.bit_state', 1); //上架
        $this->db->where('goods_base.click_count !=', 1, false); //1:前台隐藏
        $this->db->group_by('scenery_base.sid');
        $this->db->order_by('scenery_base.sid ASC');

        return $this->db->get();
    }

    /**
     * 查询金币景区
     */
    public function findBzCurrencyScenery($params)
    {
        $this->db->select('MIN(goods_base.shop_price) AS shop_price,  off_bonus, goods_base.market_price, scenery_base.sid, scenery_base.name_hanzi, scenery_base.address, scenery_info.minipics');
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sid=scenery_base.sid', 'INNER');
        $this->db->join($this->table_3 . ' AS goods_base', 'goods_base.sid=scenery_base.sid AND goods_base.bit_opt&1=1', 'INNER');
        $this->db->where('scenery_base.bit_state', 1); //上架
        $this->db->where('goods_base.is_check', 2);
        $this->db->where('goods_base.click_count !=', 1, false); //1:前台隐藏
        if (!empty($params['is_scan'])) {
            $this->db->where('goods_base.is_scan', $params['is_scan']); //1:网络预订 2:扫码订票
        }
        if ( !empty($params['off_bonus']) ) {
            $this->db->where('goods_base.off_bonus > 0');
        }

        if ( isset($params['full_bonus']) ) {
            if ( $params['full_bonus'] == 1 ) {
                $this->db->where('goods_base.off_bonus = goods_base.shop_price');
            } elseif ( $params['full_bonus'] == 2 ) {
                $this->db->where('goods_base.off_bonus <> goods_base.shop_price');
            }
        }

        switch ($params['sort']) {
            case 1:
                $this->db->order_by('goods_base.off_bonus', 'DESC');
                break;
            case 2:
                $this->db->order_by('goods_base.off_bonus', 'ASC');
                break;
            default:
                break;
        }


        $this->db->group_by('scenery_base.sid');
        if ( !empty($params['limit']) ) {
            $this->db->limit($params['limit']);
        }

        return $this->db->get();
    }

    /**
     * 找个销量最好的的景区
     * @param unknown $sid
     */
    public function sceneryFindOne($sid)
    {
        $this->db->select('MIN(goods_base.shop_price) AS shop_price, SUM(goods_base.sold_number) AS sales_volume, SUM(goods_base.advance_date) AS advance_date, SUM(goods_base.off_bonus) AS off_bonus, goods_base.market_price, scenery_base.sid, category_id, scenery_base.name_hanzi, scenery_base.name_pinyin, scenery_base.provinceid, scenery_base.cityid, scenery_base.areaid, scenery_base.address, scenery_base.star_rank, scenery_base.timestamp, scenery_info.minipics');
        $this->db->from($this->table . ' AS scenery_base');
        $this->db->join($this->table_2 . ' AS scenery_info', 'scenery_info.sid=scenery_base.sid', 'INNER');
        $this->db->join($this->table_3 . ' AS goods_base', 'goods_base.sid=scenery_base.sid AND goods_base.is_check=2 AND goods_base.bit_opt&1=1', 'INNER');
        $this->db->where('scenery_base.bit_state', 1); //上架
        if ( !empty($sid) ) {
            $this->db->where('scenery_base.sid', $sid);
        }
        $this->db->limit(1);
        return $this->db->get();
    }
}